package SQL.Spring.JdbcTemplate.DAO;

import SQL.Spring.SpringJDBC.DAO.CustomerDAO;
import SQL.Spring.SpringJDBC.Entity.Customer;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by kz on 2017/5/15.
 */
public class JdbcTemplateCustomerDAO extends JdbcDaoSupport implements CustomerDAO {
    //no need to set datasource here
    public void insert(Customer customer) {

        String sql = "INSERT INTO CUSTOMER " +
                "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";

        getJdbcTemplate().update(sql, new Object[]{customer.getCustId(),
                customer.getName(), customer.getAge()
        });

    }

    public List<Customer> findAll(){

        String sql = "SELECT * FROM CUSTOMER";

        List<Customer> customers  = getJdbcTemplate().query(sql,
                new BeanPropertyRowMapper(Customer.class));

        return customers;
    }

    public Customer findByCustomerId2(int custId) {         //通过找到的数据自动赋值内容,生成CUSTOMER
        String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";

        Customer customer = (Customer)getJdbcTemplate().queryForObject(         //返回的直接是一个OBJECT,OBJECT内部的属性已经被返回的数据库结构赋值!!!!!!
                sql, new Object[] { custId },
                new BeanPropertyRowMapper(Customer.class));     //必须用BeanPropertyRowMapper,并且这个参数为需要返回的类的类名

        return customer;
    }

    public Customer findbyCustomerName(String nm){
        String sql = "SELECT * FROM CUSTOMER WHERE NAME = ?";

        Customer customer = (Customer)getJdbcTemplate().queryForObject(
                sql,new Object[]{nm},
                new BeanPropertyRowMapper<Customer>());

        return customer;

    }

    public List<Customer> findAllIDs(){
        String sql = "SELECT * FROM CUSTOMER";
        List customers =  getJdbcTemplate().queryForList(
                sql,new BeanPropertyRowMapper<Customer>()
        );
                return customers;
    }

    public Customer findByCustomerId(int custId) {          //通过找到的数据手动赋值内容,生成CUSTOMER

        String sql = "SELECT * FROM CUSTOMER  WHERE CUST_ID = ?";


//        String sql = "SELECT * FROM items WHERE id = ?";
        Object[] params = new Object[]{custId};
//        int[] types = new int[]{Types.INTEGER};

        List items = getJdbcTemplate().query(sql, params/* 这个参数通过新建的OBJECT[],用来替代SQL中的?号 */
                ,  new ItemMapper());
        if (items.isEmpty()) {
            return null;
        }
        return (Customer) items.get(0);
    }
//        u.put("ID", rs.getInt("CUST_ID"));
//        u.put("name", rs.getString("NAME"));
//        u.put("age", rs.getInt("AGE"));


    protected class ItemMapper implements RowMapper {
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            Customer item = new Customer(
                    rs.getInt("CUST_ID"),
                    rs.getString("NAME"),
                    rs.getInt("AGE")
            );
            return item;
        }
    }

    public void insertBatch(final List<Customer> customers){

        String sql = "INSERT INTO CUSTOMER " +
                "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";

        getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Customer customer = customers.get(i);
                ps.setLong(1, customer.getCustId());
                ps.setString(2, customer.getName());
                ps.setInt(3, customer.getAge() );
            }

            @Override
            public int getBatchSize() {
                return customers.size();
            }
        });
    }

    //insert batch example with SQL     重复执行同一条语句?
    public void insertBatchSQL(final String sql){

        getJdbcTemplate().batchUpdate(sql);

    }

}
